package com.dp.appi.dao.impl;

import com.dp.appi.dao.CodesetDao;
import com.dp.appi.util.StringUtil;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by xiaoji on 2016/4/27.
 */
@Component
public class CodesetDaoImpl extends BaseDaoImpl implements CodesetDao {

    /**
     * 获取点位信息
     *
     * @return
     */
    @Override
    public List<Map<String, Object>> ptList() {
        String sql = "select pt_id,name,pt_type from p_pt where is_valid=1 and is_deleted=0";
        return getJdbcTemplate().queryForList(sql);
    }

    /**
     * 获取导线类型列表
     *
     * @return
     */
    @Override
    public List<Map<String, Object>> wireList(Map<String, String> param) {
        String sql = "select cw.wire_id,cw.voltage_grade,cw.span,cm.spec as wire_model,cw.corner,cw.section" +
                " from c_material cm" +
                " INNER JOIN c_wire cw on cm.wire_id=cw.wire_id" +
                " where cm.is_wire=1 and spec is not null and spec <> '' ORDER BY cm.spec" ;
//                " and cw.voltage_grade = "+param.get("voltage_grade");

        List<Map<String, Object>> list = getJdbcTemplate().queryForList(sql);
        return list;
    }

    /**
     * 获取典射版本
     *
     * @return
     */
    @Override
    public List<Map<String, Object>> codesetVersion(int codesetType) {
        String sql = "select codeset_version_id,codeset_type,codeset_version from c_codeset_version where is_valid=1 ";
        if(codesetType!=0) {
            sql += " and codeset_type=?";
            return getJdbcTemplate().queryForList(sql, codesetType);
        }
        return getJdbcTemplate().queryForList(sql);
    }
    @Override
    public List<Map<String, Object>> codesetVersionShouChang() {
        String sql = "select codeset_version_id,codeset_type,codeset_version from c_codeset_version where is_valid=1 ";
        sql += " and codeset_version_id=1";
        return getJdbcTemplate().queryForList(sql);
    }

    public List<Map<String, Object>> GetcodesetVersionListByProid(Long proid){
        String sql = "select codeset_version_id,codeset_type,codeset_version from c_codeset_version where codeset_type=15  ";
        sql += " and  pro_id =? ";
        return getJdbcTemplate().queryForList(sql, proid);

    }
    /**
     * 添加典设版本
     * @param pro_id
     * @param pro_name
     * @return
     */
    public Long addcodesetVersion(Long pro_id, String pro_name) {
        String sql="insert into c_codeset_version(codeset_type,codeset_version, instruction,pro_id) " +
                "values(:codeset_type,:codeset_version,:instruction,:pro_id)";
//        getJdbcTemplate().update(sql,new Object[]{encyclopedia_id,id});
        Map map = new HashMap();
        map.put("codeset_type",15);
        map.put("codeset_version",pro_name+"项目典设");
        map.put("instruction",pro_name);
        map.put("pro_id",pro_id);
        return this.executeSqlReturnKey(sql,map);
    }

    public Long addcodesetNew(Long codesetVersionid ,String codesetname, String codesetnumber,Long ptid,String keyword) {
        String sql="insert into c_codeset(name,pt_id, number,codeset_version_id,keyword,is_valid,create_time) " +
                "values(:codesetname,:ptid,:codesetnumber,:codesetVersionid,:keyword,1,NOW());";

        Map map = new HashMap();
        map.put("codesetname",codesetname);
        map.put("ptid",ptid);
        map.put("codesetnumber",codesetnumber);
        map.put("codesetVersionid",codesetVersionid);
        map.put("keyword",keyword);
        return this.executeSqlReturnKey(sql,map);

    }
    public   Long copyCodeset(Long codesetid,Long codesetVersionid) {

        String sql=" INSERT INTO c_codeset(name,pt_id,number,codeset_version_id,local_alias,drawing_cad,drawing_number,drawing_pic,drawing_thumb,original,thumb,keyword,instruction,section_min,section_max,is_valid,create_time,hg,ms,pole_emb_depth,practical_height) " +
                "SELECT name,pt_id,number,:codesetVersionid ,local_alias,drawing_cad,drawing_number,drawing_pic,drawing_thumb,original,thumb ,keyword,instruction ,section_min,section_max,is_valid,create_time,hg,ms ,pole_emb_depth,practical_height FROM" +
                " c_codeset where codeset_id=:codesetid";

        Map map = new HashMap();
        map.put("codesetid",codesetid);
        map.put("codesetVersionid",codesetVersionid);
        return this.executeSqlReturnKey(sql,map);
    }

    public   Long copyCodesetExperence(Long exploration_id,Long codesetid) {

        String sql="   INSERT INTO  c_codeset_material   (codeset_id,material_number,erp_code,name,spec,unit,num,is_wire,wire_id,obj_type,obj_id,sort)"+
                 "SELECT   :codesetid as codeset_id , material_number, erp_code,name ,spec,unit,num,is_wire,wire_id,obj_type,obj_id,sort FROM p_exploration_material where exploration_id=:exploration_id " ;
        Map map = new HashMap();
        map.put("codesetid",codesetid);
        map.put("exploration_id",exploration_id);
        return (long)this.executeSql(sql,map);
    }

    public   Long collectionCodeset(Long codesetid,String  userid){

        String sql=" INSERT INTO c_codeset(user_id,name,pt_id,number,codeset_version_id,local_alias,drawing_cad,drawing_number,drawing_pic,drawing_thumb,original,thumb,keyword,instruction,section_min,section_max,is_valid,create_time,hg,ms,pole_emb_depth,practical_height) " +
                "SELECT :userid,  name,pt_id,number,:codesetVersionid ,local_alias,drawing_cad,drawing_number,drawing_pic,drawing_thumb,original,thumb ,keyword,instruction ,section_min,section_max,is_valid,create_time,hg,ms ,pole_emb_depth,practical_height FROM" +
                " c_codeset where codeset_id=:codesetid";

        Map map = new HashMap();
        map.put("codesetid",codesetid);
        map.put("userid",userid);
        map.put("codesetVersionid",Long.valueOf(1));
        return this.executeSqlReturnKey(sql,map);
    }

    public   Long  copyCodesetMaterial(Long codesetid,Long newcodesetid) {

        String sql="INSERT INTO c_codeset_material ( codeset_id, material_number, tmp_material_number, erp_id, erp_code, name, big_category, middle_category, small_category, spec, unit, unit_weight, estimate_price, original, thumb, height, length, num, consume_steel, consume_concrete, remark, is_wire, wire_id, obj_type, obj_id, tmp_drawing_number, tmp_codeset_meme, tmp_codeset_number ,sort) \n" +
                "SELECT :newcodesetid, material_number, tmp_material_number, erp_id, erp_code, name, big_category, middle_category, small_category, spec, unit, unit_weight, estimate_price, original, thumb, height, length, num, consume_steel, consume_concrete, remark, is_wire, wire_id, obj_type, obj_id, tmp_drawing_number, tmp_codeset_meme, tmp_codeset_number ,sort" +
                " FROM   c_codeset_material where codeset_id=:codesetid";

        Map map = new HashMap();
        map.put("codesetid",codesetid);
        map.put("newcodesetid",newcodesetid);
        int num = this.executeSql(sql, map);
        return Long.valueOf(num) ;
    }

    /**
     * 添加版本材料
     * @param codesetVersionid
     * @param material_number
     * @param name
     * @param spec
     * @param num
     * @param unit
     * @return
     */
    public Long addcodesetVersionMaterial(Long codesetVersionid, String material_number,String name,String spec,String num,String unit,String type,int sort) {
        String sql="insert into c_material(material_number,erp_code, name,spec,unit,type,sort,num,codeset_version_id) " +
                "values(:material_number,:erp_code,:name,:spec,:unit,:type,:sort,:num,:codeset_version_id)";
//
        Map map = new HashMap();
        map.put("material_number",codesetVersionid.toString()+material_number);
        map.put("erp_code",material_number);
        map.put("name",name);
        map.put("spec",spec);
        map.put("unit",unit);
        map.put("type",type);
        map.put("sort",sort);
        map.put("num",num);
        map.put("codeset_version_id",codesetVersionid);
        return this.executeSqlReturnKey(sql,map);
    }
    /**
     * 修改典设材料排序
     * @param codesetVersionid
     * @return
     */
    public void updatcodesetVersionMaterial(String type,Long codesetVersionid,int sort) {
        String sql="update c_material set sort = ? " +
                "where type = ? and codeset_version_id = ?";
        getJdbcTemplate().update(sql, sort,type,codesetVersionid);
    }


    /**
     * 添加版本材料
     * @param codesetVersionid

     * @return
     */
    public Long addcodesetVersionModle(Long codesetVersionid,String name, String apec) {
        String sql="insert into c_module(name,codesetType,is_valid,codeset_version_id) " +
                "values(:name,15,1,:codesetVersionid)";
//
        Map map = new HashMap();
        map.put("name",name);
        map.put("codesetVersionid",codesetVersionid);
        long modeid = this.executeSqlReturnKey(sql,map);
        String sqlm="insert into c_module_material(module_id,material_number ,name,spec,unit ,num,is_wire) " +
                "values(:module_id,:material_number,:name,:spec,:unit,1,0)";
        Map mapm = new HashMap();
        mapm.put("module_id",modeid);
        mapm.put("material_number",modeid);
        mapm.put("name",name);
        mapm.put("spec",apec);
        mapm.put("unit","套");

        return modeid = this.executeSqlReturnKey(sqlm,mapm);
    }

    public  void delVersionMaterialByid(Long codesetVersionid)
    {
        String sql_del = "delete from c_material where codeset_version_id=?";
        getJdbcTemplate().update(sql_del, codesetVersionid);
    }
    public  void delCodesetByid(Long codesetid)
    {
        String sql_del = "delete from c_codeset_material where codeset_id=? " ;


        getJdbcTemplate().update(sql_del, codesetid);
        String sql_del1 = " delete from c_codeset where codeset_id=? ";
        getJdbcTemplate().update(sql_del1, codesetid);
    }
    public  void modfyCodesetNameById(Long codesetid,String codesetidname,String codesetnumber)
    {
        String sql_del = "UPDATE c_codeset set name=? , number=?  where codeset_id=? " ;

        getJdbcTemplate().update(sql_del, codesetidname,codesetnumber,codesetid);

    }


    public  void delcodesetMaterial(Long codesetMaterialid)
    {
        String sql_del = "delete from c_codeset_material where codeset_material_id=?";
        getJdbcTemplate().update(sql_del, codesetMaterialid);
    }
    public    void impcodesetModule(Long moduleid ,Long codesetid)
    {


        String sql = "select codeset_material_id,codeset_id,material_number,erp_id,erp_code,name,big_category," +
                "middle_category,small_category,spec,unit,unit_weight,estimate_price,original,thumb," +
                "length,height,num,consume_steel,consume_concrete,remark,obj_type,obj_id " +
                " from c_codeset_material where codeset_id=? and obj_type=1 and obj_id in (select module_material_id FROM  c_module_material where  module_id=? ) ORDER BY codeset_material_id";
        List<Map<String ,Object> > lisys = getJdbcTemplate().query(sql, new Object[]{codesetid,moduleid}, getColumnMapRowMapper());
        if(lisys==null||lisys.size()==0)
        {
            //添加模块
            String sqladd = "insert into c_codeset_material ( codeset_id,material_number, erp_id, erp_code,  name, big_category, middle_category, small_category, spec, unit, unit_weight, estimate_price, original, thumb, height, length, num, consume_steel, consume_concrete, remark, is_wire, wire_id, obj_type, obj_id) " +
                    "select ?, material_number, erp_id, erp_code,  name, big_category, middle_category, small_category, spec, unit, unit_weight, estimate_price, original, thumb, height, length, num, consume_steel, consume_concrete, remark, is_wire, wire_id, 1, module_material_id" +
                    " from c_module_material where c_module_material.module_id=? and parent_id is null";
            getJdbcTemplate().update(sqladd, new Object[]{codesetid, moduleid});

        }
        else
        {
            Map<String ,Object> map =  lisys.get(0);
            String sql_del = "update   c_codeset_material set num =?  where codeset_material_id=? ";
            Object obj=0;
            if( map.containsKey("num")&&map.get("num")!=null) {
                obj =map.get("num") ;
            }
            float numbr= Float.parseFloat(obj.toString())+1;
            Long codeset_material_id = Long.parseLong( map.get("codeset_material_id").toString());
            getJdbcTemplate().update(sql_del, new Object[]{numbr ,codeset_material_id } );
        }
    }
    public   void updateCodesetMaterial(int sort ,String number)
    {
        String sql="update  c_codeset_material set sort = ?  where material_number=?";
        getJdbcTemplate().update(sql,new Object[]{sort,number});
    }
    public   void updateModelMaterial(int sort ,String number)
    {
        String sql="update  c_module_material set sort = ?  where material_number=?";
        getJdbcTemplate().update(sql,new Object[]{sort,number});
    }
    public   void updatePexplorationMaterial(int sort ,String number)
    {
        String sql="update  p_exploration_material set sort = ?  where material_number=?";
        getJdbcTemplate().update(sql,new Object[]{sort,number});
    }


    public  void impcodesetMaterial(Long materialid ,Long codesetid)
    {


        String sql = "select codeset_material_id,codeset_id,material_number,erp_id,erp_code,name,big_category," +
                "middle_category,small_category,spec,unit,unit_weight,estimate_price,original,thumb," +
                "length,height,num,consume_steel,consume_concrete,remark,obj_type,obj_id " +
                " from c_codeset_material where codeset_id=? and obj_type=0 and obj_id =? ORDER BY codeset_material_id";
        List<Map<String ,Object> > lisys = getJdbcTemplate().query(sql, new Object[]{codesetid,materialid}, getColumnMapRowMapper());
        if(lisys==null||lisys.size()==0)
        {
            //添加材料
            String sqladd = "insert into c_codeset_material ( codeset_id,material_number, erp_id, erp_code,  name, big_category, middle_category, small_category, spec, unit, unit_weight, estimate_price, original, thumb, height, length, num, consume_steel, consume_concrete, remark, is_wire, wire_id, obj_type, obj_id , sort) " +
                    "select ?, material_number, erp_id, erp_code,  name, big_category, middle_category, small_category, spec, unit, unit_weight, estimate_price, original, thumb, height, length, 1, consume_steel, consume_concrete, remark, is_wire, wire_id, 0, material_id, sort" +
                    " from c_material where material_id=?";
            getJdbcTemplate().update(sqladd, new Object[]{codesetid, materialid});

        }
        else
        {
            Map<String ,Object> map =  lisys.get(0);
            String sql_del = "update   c_codeset_material set num = ? where   codeset_material_id = ? ";
            Object obj=0;
            if( map.containsKey("num")&&map.get("num")!=null) {
                obj =map.get("num") ;
            }
            float numbr= Float.parseFloat(obj.toString())+1;
            Long codeset_material_id = Long.parseLong( map.get("codeset_material_id").toString());
            getJdbcTemplate().update(sql_del, new Object[]{numbr ,codeset_material_id } );
        }


    }

    public void delmodleMaterial(Long model_material_id)
    {

        String sql_del = "delete from c_module_material where module_material_id=?";
        getJdbcTemplate().update(sql_del, new Object[]{model_material_id});
    }
    public void modifymodleMaterial(Long model_material_id, double num)
    {

        String sql_del = "update   c_module_material set num = ? where   module_material_id = ?";
        getJdbcTemplate().update(sql_del, new Object[]{num ,model_material_id } );
    }

    public  void impModleMaterial(Long modle_id ,String  material_number)
    {


        String sql = "select module_material_id,parent_id,material_number,erp_id,erp_code,name,big_category," +
                "middle_category,small_category,spec,unit,unit_weight,estimate_price,original,thumb," +
                "length,height,num,consume_steel,consume_concrete,remark " +
                " from c_module_material where parent_id in (select module_material_id FROM  c_module_material where  module_id=? ) and material_number =? ORDER BY module_material_id";
        List<Map<String ,Object> > lisys = getJdbcTemplate().query(sql, new Object[]{modle_id,material_number}, getColumnMapRowMapper());
        if(lisys==null||lisys.size()==0)
        {
            //添加材料

            String sqlperaid ="select module_material_id from c_module_material where module_id=? ";
            List<Map<String ,Object> > lispre = getJdbcTemplate().query(sqlperaid, new Object[]{modle_id}, getColumnMapRowMapper());

            String sqladd = "insert into c_module_material ( parent_id,material_number, erp_id, erp_code,  name, big_category, middle_category, small_category, spec, unit, unit_weight, estimate_price,original, thumb, height, length, num, consume_steel, consume_concrete, remark, is_wire ,sort) " +
                    "select ?, material_number, erp_id, erp_code,  name, big_category, middle_category, small_category, spec, unit, unit_weight, estimate_price, original, thumb, height, length, 1, consume_steel, consume_concrete, remark,0 ,sort" +
                    " from c_material where material_number=?";
            getJdbcTemplate().update(sqladd, new Object[]{lispre.get(0).get("module_material_id"), material_number});

        }
        else
        {
            Map<String ,Object> map =  lisys.get(0);
            String sql_del = "update   c_module_material set num = ? where   module_material_id = ? ";
            Object obj=0;
            if( map.containsKey("num")&&map.get("num")!=null) {
                obj =map.get("num") ;
            }
            float numbr= Float.parseFloat(obj.toString())+1;
            Long codeset_material_id = Long.parseLong( map.get("module_material_id").toString());
            getJdbcTemplate().update(sql_del, new Object[]{numbr ,codeset_material_id } );
        }


    }

    public  String versionidallcount(Long versionid ) {

        String sql_mcount = "SELECT COUNT(*) as counts FROM c_material a where  a.codeset_version_id =?";
        Map<String ,Object>  maps = findOneObject(sql_mcount, new Object[]{versionid});
        Object mcount = maps.get("counts");
        String sql_dcount = "SELECT COUNT(*) as counts FROM c_module a where  a.codeset_version_id =?";
        Map<String ,Object>  dmaps = findOneObject(sql_dcount, new Object[]{versionid});
        Object dcount = dmaps.get("counts");
        String sql_ccount = "SELECT COUNT(*) as counts FROM c_codeset a where  a.codeset_version_id =?";
        Map<String ,Object>  cmaps = findOneObject(sql_ccount, new Object[]{versionid});
        Object ccount = cmaps.get("counts");
        String  allcoutn= mcount+"|"+dcount+"|"+ccount;

        return allcoutn;
    }


    public void   changeCodesetMaterialorModle(Map<String,String> param){

        Object matId = param.get("matId");//  典设中被替换的材料ic
        String sql_mat = "select obj_type,codeset_id from c_codeset_material where codeset_material_id=?";
        Map<String, Object> result = findOneObject(sql_mat, new Object[]{matId});
        Object codeset_id = result.get("codeset_id");//材料对应的典设id
        Object obj_type = result.get("obj_type");// 是材料还是模块
        Long obj_id = Long.valueOf(param.get("obj_id"));//更新后模块后者材料的id 如果为0,表示没换，>0 表示换了

        Object num = param.get("num");//数量


        StringBuilder sb = new StringBuilder();
        if (obj_id == 0) {
            sb.append(" update c_codeset_material set num = ?");
            sb.append(" where codeset_material_id = ?");
            getJdbcTemplate().update(sb.toString(),new Object[]{num,matId});
        } else {
            //删除已经存在的踏勘点材料
            String sql_del = "delete from c_codeset_material where codeset_material_id=?";
            getJdbcTemplate().update(sql_del, new Object[]{matId});
            //添加选择则的材料或者模块（+数量+）
            if (obj_type == false) {


                sb.append("insert into c_codeset_material ( codeset_id,material_number, erp_id, erp_code,  name, big_category, middle_category, small_category, spec, unit, unit_weight, estimate_price, original, thumb, height, length, num, consume_steel, consume_concrete, remark, is_wire, wire_id, obj_type, obj_id) " +
                        "select ?, material_number, erp_id, erp_code,  name, big_category, middle_category, small_category, spec, unit, unit_weight, estimate_price, original, thumb, height, length, ?, consume_steel, consume_concrete, remark, is_wire, wire_id, 0, material_id" +
                        " from c_material where material_id=?");


            } else {
                String sqladd = "insert into c_codeset_material ( codeset_id,material_number, erp_id, erp_code,  name, big_category, middle_category, small_category, spec, unit, unit_weight, estimate_price, original, thumb, height, length, num, consume_steel, consume_concrete, remark, is_wire, wire_id, obj_type, obj_id) " +
                        "select ?, material_number, erp_id, erp_code,  name, big_category, middle_category, small_category, spec, unit, unit_weight, estimate_price, original, thumb, height, length, ?, consume_steel, consume_concrete, remark, is_wire, wire_id, 1, module_material_id" +
                        " from c_module_material where module_material_id=? and parent_id is null";
            }
            getJdbcTemplate().update(sb.toString(), new Object[]{codeset_id, num,  obj_id});
        }
    }


    /**
     * 获取典设列表
     *
     * @param name               典设名称
     * @param codeset_version_id 典设版本
     * @param pt_id              典设类型
     * @param keyword            查询关键字
     * @param s                  开始序列
     * @param n                  获取条数
     * @return
     */
    @Override
    public List<Map<String, Object>> codesetList(String filter, String name, String codeset_version_id, String pt_id,
                                                 String keyword, Integer s, Integer n) {
        StringBuffer sql = new StringBuffer();
        sql.append("select codeset_id,codeset_version_id,pt_id,name,number,local_alias,thumb,original,keyword from c_codeset ");
        sql.append(" where is_valid=1 ");
        List paramList = new ArrayList();
        if (!StringUtils.isEmpty(name)&&name!=null) {
            sql.append(" and name like ? ");
            paramList.add("%" + name + "%");
        }
        if (!StringUtils.isEmpty(codeset_version_id)&&codeset_version_id!=null) {
            sql.append(" and codeset_version_id= ? ");
            paramList.add(codeset_version_id);
        }else
        {
            sql.append(" and codeset_version_id= ? ");
            paramList.add("-1");
        }


        if (!StringUtils.isEmpty(pt_id)&& pt_id!=null) {
            sql.append("  and pt_id=? ");
            paramList.add(pt_id);
        }
        if (!StringUtils.isEmpty(keyword)&&keyword!=null) {
            sql.append("  and keyword like ? ");
            paramList.add("%" + keyword + "%");
        }
        if (!StringUtils.isEmpty(filter)&&filter!=null) {
            sql.append(" and ( ");
            String[] filterArr = filter.split(",");
            for (int i = 0; i < filterArr.length; i++) {
                if (i != 0) {
                    sql.append(" and ");
                }
                if (filterArr[i] != "") {
                    sql.append(" keyword like ? ");
                    paramList.add("%" + filterArr[i] + "%");
                }
            }
            sql.append(" ) ");
        }
        paramList.add(s);
        paramList.add(n);
        sql.append(" ORDER BY codeset_id desc limit ?,?");
        return getJdbcTemplate().queryForList(sql.toString(), paramList.toArray());
    }

    @Override
    public List<Map<String,Object>> modleList(String name,String codeset_version_id,Integer s,Integer n)
    {
        StringBuffer sql = new StringBuffer();
        sql.append("select a.module_id ,a.name,b.spec ,b.unit, b.num from c_module a ");
        sql.append(" left join c_module_material b  on  a.module_id= b.module_id   where   a.is_valid=1");
        List paramList = new ArrayList();
        if (!StringUtils.isEmpty(name)&&name!=null) {
            sql.append(" and a.name like ? ");
            paramList.add("%" + name + "%");
        }
        if (!StringUtils.isEmpty(codeset_version_id)&&codeset_version_id!=null) {
            sql.append(" and a.codeset_version_id= ? ");
            paramList.add(codeset_version_id);
        }



        paramList.add(s);
        paramList.add(n);
        sql.append(" ORDER BY a.module_id desc limit ?,?");
        return getJdbcTemplate().queryForList(sql.toString(), paramList.toArray());
    }





    /**
     * 获取典设材料
     *
     * @param id 典设id
     * @return
     */
    @Override
    public List<Map<String, Object>> codesetMaterial(Long id) {
        String sql = "select codeset_material_id,codeset_id,material_number,erp_id,erp_code,name,big_category," +
                "middle_category,small_category,spec,unit,unit_weight,estimate_price,original,thumb," +
                "length,height,num,consume_steel,consume_concrete,remark,obj_type " +
                " from c_codeset_material where codeset_id=? ORDER BY sort,codeset_material_id";
        return getJdbcTemplate().query(sql, new Object[]{id}, getColumnMapRowMapper());
//        return getJdbcTemplate().queryForList(sql,id);
    }
    public List<Map<String, Object>> modelMaterial(Long modleid) {
        String sql = "select module_material_id as codeset_material_id ,module_id,material_number,erp_id,name,spec," +
                "unit,num,spec,thumb,'0' as obj_type" +
                " from c_module_material where parent_id in(" +
                " select module_material_id FROM  c_module_material where module_id=? " +
                ") order by sort";
        return getJdbcTemplate().query(sql, new Object[]{modleid}, getColumnMapRowMapper());

    }

    @Override
    public List<Map<String, Object>> likeNameMList(String versionid,Long matId, Integer s, Integer n) {
        List<Map<String, Object>> list = null;
        //踏勘点的典设材料或者模块对象
        String sqlObj = "select pm.material_id,pm.`name`,pm.obj_type from p_exploration_material pm where pm.material_id = ?";
        Map<String, Object> map = findOneForJdbc(sqlObj, new Object[]{matId});
        if (map != null) {
            Object name = map.get("name");
            Object obj_type = map.get("obj_type");
            String sqlList = "";
            if (obj_type == false)
                sqlList = "select cm.material_id obj_id,cm.`name`,cm.spec from c_material cm where cm.`name` = ? and cm.codeset_version_id = ? order by cm.material_id LIMIT ?,?";
            else
                sqlList = "select cm.module_id   obj_id,cm.`name`,cm.spec from c_module_material cm where ISNULL(cm.parent_id) and cm.`name` = ? and cm.codeset_version_id=?  order by cm.module_id LIMIT ?,?";

            list = findForJdbc(sqlList, new Object[]{name,versionid, s, n});
        }
        return list;
    }
    @Override
    public List<Map<String, Object>> likeNameCodesetMaterial(String versionid,Long matId, Integer s, Integer n) {
        List<Map<String, Object>> list = null;
        //踏勘点的典设材料或者模块对象
        String sqlObj = "select pm.name ,pm.obj_type from c_codeset_material pm where pm.codeset_material_id = ?";
        Map<String, Object> map = findOneForJdbc(sqlObj, new Object[]{matId});
        if (map != null) {
            Object name = map.get("name");
            Object obj_type = map.get("obj_type");
            String sqlList = "";
            if (obj_type == false)
                sqlList = "select cm.material_id obj_id,cm.name,cm.spec from c_material cm where cm.name = ? and cm.codeset_version_id = ? order by cm.material_id LIMIT ?,?";
            else
                sqlList = "select cm.module_id   obj_id,cm.name,cm.spec from c_module_material cm where ISNULL(cm.parent_id) and cm.`name` = ? and cm.codeset_version_id=?  order by cm.module_id LIMIT ?,?";

            list = findForJdbc(sqlList, new Object[]{name,versionid, s, n});
        }
        return list;
    }

}